package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.maszy.bean.Car;
import com.maszy.db.DBConnection;

public class CarService {
	Car service=new Car(); 
	private static PreparedStatement pst;
	private static ResultSet rs = null;
	public  int addCar(Car obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql ="INSERT INTO [xiahe].[dbo].[Maszy_Car]([Maszy_Car_NO], [Maszy_Car_Type], [Maszy_Car_Driver], [Maszy_Car_Status], [Maszy_Car_Date], [Maszy_Car_Remark]) VALUES(?,?,?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			pst.setString(1,obj.getCar_NO());
			pst.setString(2,obj.getCar_Type());
			pst.setString(3, obj.getCar_Driver());
			pst.setString(4, obj.getCar_Status());
			pst.setString(5, obj.getCar_Date());
			pst.setString(6, obj.getCar_Remark());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}
	public  int addCarMilage(Car obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql ="INSERT INTO [xiahe].[dbo].[Maszy_CarMilage]([Maszy_Car_Id],[Maszy_CarMilage_Milage], [Maszy_CarMilage_Oil], [Maszy_CarMilage_Date], [Maszy_CarMilage_Remark]) VALUES(?,?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			pst.setInt(1, obj.getCar_Id());
			pst.setDouble(2, obj.getCarMilage_Milage());
			pst.setDouble(3, obj.getCarMilage_Oil());
			pst.setString(4, obj.getCarMilage_Date());
			pst.setString(5,obj.getCarMilage_Remark());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}
	public  int addCarPay(Car obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql ="INSERT INTO [xiahe].[dbo].[Maszy_CarPay]([Maszy_Car_Id], [Maszy_CarPay_Type],[Maszy_CarPay_Content], [Maszy_CarPay_Pay], [Maszy_CarPay_Date], [Maszy_CarPay_Remark],[Maszy_CarPay_OilCount]) VALUES(?,?,?,?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			pst.setInt(1, obj.getCar_Id());
			pst.setString(2, obj.getCarPay_Type());
			pst.setString(3, obj.getCarPay_Content());
			pst.setDouble(4, obj.getCarPay_Pay());
			pst.setString(5, obj.getCarPay_Date());
			pst.setString(6,obj.getCarPay_Remark());
			pst.setDouble(7,obj.getCarPay_OilCount());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}
	public int update(Car obj){
		DBConnection con = new DBConnection();
		int i=0;
		String sql="update Maszy_DoorStore set Maszy_DoorStore_Name=?,Maszy_DoorStore_Adress=?,Maszy_DoorStore_Remark=? where Maszy_DoorStore_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);			
			i=pst.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}
	public int delete(Car obj){
		DBConnection con = new DBConnection();
		int i=0;
		String sql="delete from  Maszy_DoorStore  where Maszy_DoorStore_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);	
//			pst.setLong(1, obj.getDoorStore_Id());
			i=pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}
	public Car queryById(Car obj){
		DBConnection con = new DBConnection();
		String sql="select * from Maszy_DoorStore where Maszy_DoorStore_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);	
//			pst.setLong(1, obj.getDoorStore_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
//				obj=new DoorStore();
//				obj.setDoorStore_Id(rs.getLong("Maszy_DoorStore_Id"));
//				obj.setDoorStore_Name(rs.getString("Maszy_DoorStore_Name"));
//				obj.setDoorStore_Adress(rs.getString("Maszy_DoorStore_Adress"));
//				obj.setDoorStore_Remark(rs.getString("Maszy_DoorStore_Remark"));	
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}
	public List<Car> queryAllCar(){
		List<Car> list=new ArrayList<Car>();
		DBConnection con = new DBConnection();
		String sql="select * from Maszy_Car";
		try {
			pst = con.getConnection().prepareStatement(sql);				
			rs = pst.executeQuery();
	
			while (rs.next()) {
				Car obj=new Car();
					obj.setCar_Id(Integer.parseInt(rs.getString("Maszy_Car_Id")));
					obj.setCar_NO(rs.getString("Maszy_Car_NO"));
					obj.setCar_Type(rs.getString("Maszy_Car_Type"));
					obj.setCar_Driver(rs.getString("Maszy_Car_Driver"));
					String s=rs.getString("Maszy_Car_Status");
					if("1".equals(s)){
						obj.setCar_Status("闲置");
					}else if("2".equals(s)){
						obj.setCar_Status("正在使用");
					}else if("3".equals(s)){
						obj.setCar_Status("故障维修");
					}
					
					obj.setCar_Date(rs.getString("Maszy_Car_Date"));
					obj.setCar_Remark(rs.getString("Maszy_Car_Remark"));
				list.add(obj);
			}			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}	
	public Map queryCarReport(int id,String bTime,String eTime){
		Map<String, Object> map = new HashMap<String, Object>();
		DBConnection con = new DBConnection();
		String sql="EXEC carreport ?,?,?";
		Date d = new Date();
	    DateFormat format = new SimpleDateFormat("yyyy-MM-dd mm:HH:ss");
	     

		try {
			pst = con.getConnection().prepareStatement(sql);	
			pst.setInt(1, id);
			pst.setString(2, bTime+" 00:00:00");
			pst.setString(3, eTime+" 23:59:59");
			rs = pst.executeQuery();
			while (rs.next()) {
				
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));					
				}
				
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return map;
	}
}
